'''
界址点表
'''
import xlrd
import openpyxl
from openpyxl.styles import Border, Side, colors
from openpyxl.styles import Alignment
from openpyxl.styles import Font
from openpyxl import Workbook


class JZDForm(object):
    def __init__(self):
        self.face = []
        self.jlist = []
        self.pointx = []
        self.pointy = []
        # self.jlist_first = []
        # self.pointx_first = []
        # self.pointy_first = []
        self.rowlist = []
        self.mj = []
        self.mjm = []

    def readJZD(self, filepath):
        file = filepath

        workbook = xlrd.open_workbook(file)  # 创建工作空间
        sheet1 = workbook.sheet_by_index(0)  # 选取第0个表

        mj1 = (sheet1.col_values(1))
        mj1.pop(0)

        mjm1 = (sheet1.col_values(2))
        mjm1.pop(0)

        face1 = (sheet1.col_values((3)))
        # 把第一行标题弹出去
        face1.pop(0)

        jlist1 = (sheet1.col_values(4))
        jlist1.pop(0)

        pointx1 = (sheet1.col_values((5)))
        pointx1.pop(0)

        pointy1 = (sheet1.col_values((6)))
        pointy1.pop(0)

        # 添加数据
        n1 = len(face1)
        for a1 in range(0, n1):
            self.face.append(int(face1[a1]))

        n2 = len(jlist1)
        for a2 in range(0, n2):
            self.jlist.append(jlist1[a2])
            self.rowlist.append(int(jlist1[a2][1:]))

        n3 = len(pointx1)
        for a3 in range(0, n3):
            self.pointx.append(pointx1[a3])

        n4 = len(pointy1)
        for a4 in range(0, n4):
            self.pointy.append(pointy1[a4])

        n5 = len(mj1)
        for a5 in range(0, n5):
            self.mj.append(mj1[a5])

        n6 = len(mjm1)
        for a6 in range(0, n6):
            self.mjm.append(mjm1[a6])

        # i = 0
        # for e in jlist1:
        #     if e == 'J1':
        #         self.jlist_first.append(e)
        #         self.pointx_first.append(pointx1[i])
        #         self.pointy_first.append(pointy1[i])
        #     i = i + 1

    def writeJZD(self):
        wb = Workbook()

        # 边框设置
        border_set = Border(left=Side(style='medium', color=colors.BLACK),
                            right=Side(style='medium', color=colors.BLACK),
                            top=Side(style='medium', color=colors.BLACK),
                            bottom=Side(style='medium', color=colors.BLACK))
        # 字体设置
        font1 = Font(name=u'Calibri', bold=False, italic=False, size=10.5)

        n = 0
        ws = locals()
        for i in self.jlist:

            # 通过J1判断分别写入表
            if i == 'J1':
                ws['ws' + str(self.face[n])] = wb.create_sheet(index=self.face[n], title='Sheet' + str(self.face[n]))
                # 写入表头和样式
                ws['ws' + str(self.face[n])].cell(row=1, column=1).value = '点号'
                ws['ws' + str(self.face[n])].cell(row=1, column=2).value = 'X坐标'
                ws['ws' + str(self.face[n])].cell(row=1, column=3).value = 'Y坐标'
                ws['ws' + str(self.face[n])].cell(row=1, column=4).value = '面积'
                ws['ws' + str(self.face[n])].cell(row=1, column=5).value = '面积亩'

                ws['ws' + str(self.face[n])].column_dimensions['B'].width = 20
                ws['ws' + str(self.face[n])].column_dimensions['C'].width = 20
                ws['ws' + str(self.face[n])].column_dimensions['D'].width = 20
                ws['ws' + str(self.face[n])].column_dimensions['E'].width = 20

                for i1 in range(1, 6):
                    ws['ws' + str(self.face[n])].cell(row=1, column=i1).border = border_set
                    ws['ws' + str(self.face[n])].cell(row=1, column=i1).font = font1
                    ws['ws' + str(self.face[n])].cell(row=1, column=i1).alignment = Alignment(horizontal='center',
                                                                                              vertical='center',
                                                                                              text_rotation=0,
                                                                                              wrap_text=True,
                                                                                              shrink_to_fit=False,
                                                                                              indent=0)
                # wb.save('test.xlsx')
            n = n + 1

        # 写入数据和样式
        n1 = 0
        for j in self.face:

            # 确定表，并在每个表写入数据
            if j:
                sheet1 = wb['Sheet{0}'.format(j)]
                sheet1.cell(row=int(self.rowlist[n1]) + 1, column=1).value = self.jlist[n1]
                sheet1.cell(row=int(self.rowlist[n1]) + 1, column=2).value = self.pointy[n1]
                sheet1.cell(row=int(self.rowlist[n1]) + 1, column=3).value = self.pointx[n1]
                sheet1.cell(row=int(self.rowlist[n1]) + 1, column=4).value = self.mj[n1]
                sheet1.cell(row=int(self.rowlist[n1]) + 1, column=5).value = self.mjm[n1]

                wb.save('test.xlsx')

                for j1 in range(1, 6):
                    # 设置插入行的单元格格式
                    sheet1.cell(row=int(self.rowlist[n1]) + 1, column=j1).border = border_set
                    # 设置单元格字体
                    sheet1.cell(row=int(self.rowlist[n1]) + 1, column=j1).font = font1
                    # 设置插入行的单元格数字格式为保留三位小数
                    sheet1.cell(row=int(self.rowlist[n1]) + 1, column=j1).number_format = '0.000'
                    # 单元格居中
                    sheet1.cell(row=int(self.rowlist[n1]) + 1, column=j1).alignment = Alignment(horizontal='center',
                                                                                                vertical='center',
                                                                                                text_rotation=0,
                                                                                                wrap_text=True,
                                                                                                shrink_to_fit=False,
                                                                                                indent=0)

            n1 = n1 + 1
        # 遍历所有表，修改最后J1
        sheets = wb.sheetnames
        for j1 in range(1, len(sheets)):
            sheet = wb[sheets[j1]]
            sheet.cell(row=sheet.max_row, column=1).value = 'J1'

        # 删除多余表
        ws = wb['Sheet']
        wb.remove(ws)
        wb.save('test.xlsx')



